package com.sino.uam.support;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;

public class UAMDBManager {

    private final Logger logger = Logger.getLogger(UAMDBManager.class);

    private Map<String, String> sqls = new ConcurrentHashMap<String, String>();

    private static UAMDBManager inst = new UAMDBManager();

    public static UAMDBManager getInst() {
        return inst;
    }

    private UAMDBManager() {
        try {
            InputStream ins = UAMDBManager.class.getResourceAsStream("sql.properties");
            Properties props = new Properties();
            props.load(ins);
            IOUtils.closeQuietly(ins);
            Set<String> knames = props.stringPropertyNames();
            for (String key : knames) {
                sqls.put(key, props.getProperty(key));
            }
        } catch (Exception e) {
            logger.fatal("Can not read sql.properties file. Look what is happening. It will crash probably!!!", e);
        }
    }

    @SuppressWarnings("unchecked")
    public <T> List<T> queryBeanList(String sqlKey, Class<T> clazz, Object... params) throws SQLException {
        String sql = getSqlFromKey(sqlKey);
        QueryRunner run = new QueryRunner(UAMDataSource.getDataSource());
        List<T> result = null;
        try {
            if (params == null || params.length == 0)
                result = (List<T>) run.query(sql, new BeanListHandler(clazz));
            else
                result = (List<T>) run.query(sql, new BeanListHandler(clazz), params);
        } catch (SQLException e) {
            logger.error("Error while querying... Key:" + sqlKey + " params: " + params, e);
            throw e;
        }
        return result;
    }

    @SuppressWarnings("unchecked")
    public <T> T queryBean(String sqlKey, Class<T> clazz, Object... params) throws SQLException {
        String sql = getSqlFromKey(sqlKey);
        QueryRunner run = new QueryRunner(UAMDataSource.getDataSource());
        T result = null;
        try {
            if (params == null || params.length == 0)
                result = (T) run.query(sql, new BeanHandler(clazz));
            else
                result = (T) run.query(sql, new BeanHandler(clazz), params);
        } catch (SQLException e) {
            logger.error("Error while querying... Key:" + sqlKey + " params: " + params, e);
            throw e;
        }
        return result;
    }

    public List<Map<String, Object>> queryList(String sqlKey, Object... params) throws SQLException {
        String sql = getSqlFromKey(sqlKey);
        QueryRunner run = new QueryRunner(UAMDataSource.getDataSource());
        List<Map<String, Object>> result = null;
        try {
            if (params == null || params.length == 0)
                result = run.query(sql, new MapListHandler());
            else
                result = run.query(sql, new MapListHandler(), params);
        } catch (SQLException e) {
            logger.error("Error while querying... Key:" + sqlKey + " params: " + params, e);
            throw e;
        }
        return result;
    }

    public Map<String, Object> queryItem(String sqlKey, Object... params) throws SQLException {
        String sql = getSqlFromKey(sqlKey);
        QueryRunner run = new QueryRunner(UAMDataSource.getDataSource());
        Map<String, Object> result = null;
        try {
            if (params == null || params.length == 0)
                result = run.query(sql, new MapHandler());
            else
                result = run.query(sql, new MapHandler(), params);
        } catch (SQLException e) {
            logger.error("Error while querying... Key:" + sqlKey + " params: " + params, e);
            throw e;
        }
        return result;
    }

    public int update(String sqlKey, Object... params) throws SQLException {
        String sql = getSqlFromKey(sqlKey);
        QueryRunner run = new QueryRunner(UAMDataSource.getDataSource());
        if (params == null || params.length == 0)
            return run.update(sql);
        else
            return run.update(sql, params);
    }

    private String getSqlFromKey(String sqlKey) throws SQLException {
        String sql = sqls.get(sqlKey);
        if (sql == null)
            return sqlKey;//throw new SQLException("could not pick sql from config file");
        else
            return sql;
    }
}
